package weaver.workflow.actionsupport;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map; 

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import weaver.conn.RecordSet;
import weaver.conn.RecordSetDataSource;
import weaver.general.Util;

public class DBUtil {
	private static Log log = LogFactory.getLog("DataBaseOperation.class");
	private String datasourceId;
	
	public DBUtil(){
		this.datasourceId = "";
	}
	public DBUtil(String datasourceId){
		this.datasourceId = datasourceId;
	}

	public Map<String,String> searchBySql(String tableName){
		return searchBySql(null,tableName,"");
	}

	public Map<String,String> searchBySql(String tableName,String where){
		return searchBySql(null,tableName,where);
	}
	
	public int getRowNum(String tableName,String where){
		RecordSetDataSource rsds=new RecordSetDataSource(datasourceId);
		if(where!=null &&!where.equals("")){
			where="where "+where;
		}
		String sql=String.format("select count(*) as num from %s %s", tableName, Util.null2String(where));
		rsds.execute(sql);
		rsds.next();
		return rsds.getInt("num");
	}
	
	public Map<String,String> searchBySql(String field,String tableName,String where){
		RecordSetDataSource rsds=new RecordSetDataSource(datasourceId);
		Map<String,String> map=new HashMap<String,String>();
		if(field==null||field.equals("")){
			field="*";
		}
		if(where!=null &&!where.equals("")){
			where="where "+where;
		}
		String sql=String.format("select %s from %s %s" , field,tableName,Util.null2String(where));
		rsds.execute(sql);
		if(rsds.next()){
			for(String colName : rsds.getColumnName()){		
				map.put(colName.toLowerCase(), Util.null2String(rsds.getString(colName)));
			}
		}
		return map;
	}

	public int deleteBySql(String tableName,String where){
		String resultSql="delete from "+tableName;
		if(where!=null&&!"".equals(where)){
			resultSql+=" where "+where;
		}		
		if("".equals(datasourceId) || datasourceId==null){
			return new RecordSet().execute(resultSql)?1:0;
		}else{
			return new RecordSetDataSource(datasourceId).execute(resultSql)?1:0;
		}
	}
	
	public int updateByFields(String...vals){
		Map<String,String> datamap = new HashMap<String,String>();
		String table = vals[0];
		String where = vals[vals.length-1];
		for(int i=1; i<vals.length-2; i=i+2){
			datamap.put(vals[i].toLowerCase(), vals[i+1].toLowerCase());	
		}
		return updateByMap(table, datamap, where);
	}
	
	public int updateByMap(String tableName,Map<String,String> map,String where){
		if(map==null||map.isEmpty()||tableName==null||tableName.equals("")){
			return 0;
		}
		List<String> colList=getTableColList(tableName);
		StringBuffer sql=new StringBuffer();
		String whereStr="";
		if(where !=null ||!"".equals(where)){
			whereStr=" where "+where;
		}
		sql.append("update ").append(tableName).append(" set ");
		for(String key:colList){
			if(map.containsKey(key.toLowerCase())){
				sql.append(key).append("='");
				sql.append(map.get(key)).append("',");
			}
		}
		String resultSql=sql.substring(0,sql.length()-1)+whereStr;
		if("".equals(datasourceId) || datasourceId==null){
			return new RecordSet().execute(resultSql)?1:0;
		}else{
			return new RecordSetDataSource(datasourceId).execute(resultSql)?1:0;
		}
	}
	
	public int insertByMap(String tableName,Map<String,String> map){
		int result=0;
		if(map==null||map.isEmpty()||tableName==null||tableName.equals("")){
			return result;
		}
		List<String> realList=new ArrayList<String>();

		String resultSql=spiltInsertString(tableName,map,realList);
		log.error("resultSql=insert==="+resultSql);
		if("".equals(datasourceId) || datasourceId==null){
			return new RecordSet().execute(resultSql)?1:0;
		}else{
			return new RecordSetDataSource(datasourceId).execute(resultSql)?1:0;
		}
	}

	private String spiltInsertString(String tableName,Map<String,String> map,List<String> realList){
		List<String> colList=getTableColList(tableName);
		StringBuffer sql=new StringBuffer();
		StringBuffer sqlValues=new StringBuffer();
		sql.append("insert into ").append(tableName).append("(");
		for(String temp:colList){
			log.error("temp==="+temp);
			if(map.containsKey(temp)){
				sql.append(temp).append(",");
				if(map.get(temp).contains("nextval")){
					sqlValues.append(map.get(temp)).append(",");
				}else{
					sqlValues.append("'").append(map.get(temp)).append("'").append(",");
				}
			}
		}
		String resultSql=sql.substring(0,sql.length()-1)+") values("+sqlValues.substring(0,sqlValues.length()-1)+")";
		return resultSql;
	}

	public int deleteRow(String tableName,String where){
		StringBuffer sql=new StringBuffer();
		if(tableName==null||"".equals(tableName)){
			return 0;
		}
		sql.append("delete ").append(tableName).append(" ");
		if(!(where==null||"".equals(where))){
			sql.append("where ").append(where);
		}	
		if("".equals(datasourceId) || datasourceId==null){
			return new RecordSet().execute(sql.toString())?1:0;
		}else{
			return new RecordSetDataSource(datasourceId).execute(sql.toString())?1:0;
		}
	}

	public List<Map<String,String>> searchList(String tableName){
		return searchList(null,tableName,"");
	}

	public List<Map<String,String>> searchList(String tableName,String where){
		return searchList(null,tableName,where);
	}


	public List<String> getTableColList(String tablename){
		RecordSetDataSource rsds=new RecordSetDataSource(datasourceId);
		List<String> colList=new ArrayList<String>();
		rsds.execute("SELECT top 1 * FROM "+tablename.toLowerCase());
		for(String colName : rsds.getColumnName()){
			colList.add(colName.toLowerCase());
		}
		return colList;
	}

	public List<Map<String,String>> searchList(String field,String tableName,String where){
		RecordSetDataSource rsds=new RecordSetDataSource(datasourceId);
		List<Map<String,String>> list=new ArrayList<Map<String,String>>();
		if(field==null||field.equals("")){
			field="*";
		}
		if(where!=null &&!where.equals("")){
			where="where "+where;
		}
		String sql=String.format("select %s from %s %s" , field,tableName,Util.null2String(where));
		rsds.execute(sql);
		while(rsds.next()){
			Map<String,String> map=new HashMap<String,String>();
			for(String colName : rsds.getColumnName()){
				map.put(colName.toLowerCase(), Util.null2String(rsds.getString(colName)));
			}
			list.add(map);
		}
		return list;
	}
}
